# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
'''
date:2020-2-2
name:chx
'''
import json

from pymysql import connect
import time
from Spiders import settings


class SpidersPipeline(object):
    def __init__(self):
        self.connect=connect(
            host=settings.SQLHOST,
            db=settings.SQLDATABASE,
            user=settings.SQLUSER,
            passwd=settings.SQLPASSWORD,
            charset=settings.SETCHAR
        )
        self.cursor=self.connect.cursor()
        self.date=time.strftime("%Y_%m_%d_%H",time.localtime(time.time()))
        self.dates = time.strftime("%Y-%m-%d %H:%M", time.localtime(time.time()))
        tablename='city_'+str(self.date)
        open('Spiders/data/filename/mysql_tablename','a').writelines(tablename+'\n')
        city='CREATE TABLE IF NOT EXISTS city_'+str(self.date)+'(' \
            'id INT PRIMARY KEY AUTO_INCREMENT,' \
            'city VARCHAR(100),' \
            'diagnosed INT(100),' \
            'suspected INT(100),'  \
            'cured INT(100),' \
            'died INT(100)' \
            ') '
        # self.cursor.execute(sql)
        total='CREATE TABLE IF NOT EXISTS total_'+str(self.date)+'(' \
            'id INT PRIMARY KEY AUTO_INCREMENT,' \
            'dates VARCHAR(100),' \
            'diagnosed INT(100),' \
            'suspected INT(100),' \
            'cured INT(100),' \
            'died INT(100)' \
            ') '
        tablename1 = 'total_' + str(self.date)
        open('Spiders/data/filename/mysql_tablename', 'a').writelines(tablename1 + '\n')
        self.cursor.execute(city)
        self.cursor.execute(total)
    def process_item(self, item, spider):
        for i in item['detail']:
            sel='select city from city_data where city={}'.format('\''+str(i['city'])+'\'')
            tmp=self.cursor.execute(sel)
            if(int(tmp)==0):
                sqls = 'insert into city_data (city,diagnosed,suspected,cured,died,uptime) value({},{},{},{},{},{})' \
                          .format('\'' + str(i['city']) + '\'', int(i['diagnosed']), int(i['suspected']), int(i['cured']), int(i['died']),'\''+str(self.dates)+'\'')
                self.cursor.execute(sqls)
                self.connect.commit()

            else:
                sqls='UPDATE city_data SET diagnosed={},suspected={},cured={},died={},uptime={} WHERE city={}'.format(int(i['diagnosed']), int(i['suspected']), int(i['cured']), int(i['died']),'\''+str(self.dates)+'\'','\'' + str(i['city']) + '\'')
                self.cursor.execute(sqls)
                self.connect.commit()
            sql = 'insert into city_' + str(self.date) + '(city,diagnosed,suspected,cured,died) value({},{},{},{},{})'\
                .format('\'' + str(i['city']) + '\'', int(i['diagnosed']), int(i['suspected']),int(i['cured']), int(i['died']))
            self.cursor.execute(sql)
            self.connect.commit()
        for i in item['timeline']:

            tisel='SELECT dates FROM total_data WHERE dates={}'.format('\''+str(i)+'\'')
            tmps=self.cursor.execute(tisel)
            if(int(tmps)==0):
                tisql='insert into total_data (dates,diagnosed,suspected,cured,died) value({},{},{},{},{})' \
                .format('\'' + str(i) + '\'', +int(item['timeline'][i]['diagnosed']),int(item['timeline'][i]['suspected']),int(item['timeline'][i]['cured']),int(item['timeline'][i]['died']))
                self.cursor.execute(tisql)
                self.connect.commit()
            else:
                tisql='UPDATE total_data SET diagnosed={},suspected={},cured={},died={} WHERE dates={}'.format(int(item['timeline'][i]['diagnosed']),int(item['timeline'][i]['suspected']),int(item['timeline'][i]['cured']),int(item['timeline'][i]['died']),'\'' + str(i) + '\'')
                self.cursor.execute(tisql)
                self.connect.commit()
            sql = 'insert into total_' + str(self.date) + '(dates,diagnosed,suspected,cured,died) value({},{},{},{},{})' \
                .format('\'' + str(i) + '\'', +int(item['timeline'][i]['diagnosed']),int(item['timeline'][i]['suspected']),int(item['timeline'][i]['cured']),int(item['timeline'][i]['died']))

            self.cursor.execute(sql)
            self.connect.commit()
        # print(self.date)
        return item
    def close_spider(self):
        self.cursor.close()
        self.connect.close()

class filejson(object):
    def __init__(self):
        self.date=time.strftime("%Y_%m_%d_%H_%M",time.localtime(time.time()))
        self.city=open('Spiders/data/city/city_'+str(self.date)+'.json','w')
        self.total = open('Spiders/data/total/total_' + str(self.date) + '.json', 'w')
        crtyname='city_'+str(self.date)+'.json'
        totalname='total_' + str(self.date) + '.json'
        open('Spiders/data/filename/jsonfile_city','a').writelines(crtyname+'\n')
        open('Spiders/data/filename/jsonfile_total','a').writelines(totalname+'\n')

    def process_item(self, item, spider):
        for i in item['detail']:
            data={'city':i['city'],'diagnosed':int(i['diagnosed']),'suspected':int(i['suspected']),'cured':int(i['cured']),'diea':int(i['died'])}
            self.city.write(json.dumps(dict(data),ensure_ascii=False)+'\n')
        for i in item['timeline']:
            datas={'date':str(i),'diagnosed':int(item['timeline'][i]['diagnosed']),'suspected':int(item['timeline'][i]['suspected']),'cured':int(item['timeline'][i]['cured']),'died':int(item['timeline'][i]['died'])}
            # print(datas)
            self.total.write(json.dumps(dict(datas),ensure_ascii=False)+'\n')
    def close_spider(self):
        self.city.close()
        self.total.close()